package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.ActivityRechargeDataDto;
import com.dy.yunying.api.datacenter.vo.ActivityRechargeDataVO;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import com.pig4cloud.pig.common.core.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @Author: hma
 * @Date: 2022/9/05 13:44
 */
@Slf4j
@Component(value = "activityRechargeDataDao")
public class ActivityRechargeDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 活跃付费明细列表总数查询
	 *
	 * @param req
	 * @return
	 */
	public Long countDataTotal(ActivityRechargeDataDto req) {
		final StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(this.getResultSql(req));
		countSql.append("    )\n");

		log.info("活跃付费明细总条数查询SQL: [\n{}]", countSql.toString());
		long start = System.currentTimeMillis();

		Long count = clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
		long end = System.currentTimeMillis();
		log.info("活跃付费明细总条数查询耗时: {}ms", end - start);
		return count;
	}


		/**
		 * 活跃付费明细列表总数查询
		 *
		 * @param req
		 * @return
		 */
		public List<ActivityRechargeDataVO> collect(ActivityRechargeDataDto req) {
			final StringBuilder collectSql = new StringBuilder();
			collectSql.append("select "+
					"sum(totalFee) totalFee" +
					" ,sum(fee) fee " +
					",sum(giveMoney)  giveMoney\n" );
			collectSql.append("FROM\n");
			collectSql.append("    (\n");
			collectSql.append(this.getResultSql(req));
			collectSql.append("    )\n");

			log.info("活跃付费明细汇总查询SQL: [\n{}]", collectSql.toString());
			long start = System.currentTimeMillis();

			List<ActivityRechargeDataVO> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ActivityRechargeDataVO.class));
			long end = System.currentTimeMillis();
			log.info("活跃付费明细汇总查询耗时: {}ms", end - start);
		   return  list;

	}

	/**
	 * 广告数据分析表分页列表查询
	 *
	 * @param req
	 * @return
	 */
	public List<ActivityRechargeDataVO> list(ActivityRechargeDataDto req) {
		final StringBuilder sql = this.getResultSql(req);
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    payTime DESC\n");
		}

		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}

		log.info("活跃付费明细列表页查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();

		List<ActivityRechargeDataVO> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ActivityRechargeDataVO.class));
		long end = System.currentTimeMillis();
		log.info("活跃付费明细列表查询耗时: {}ms", end - start);
		return list;
	}


	private StringBuilder getResultSql(ActivityRechargeDataDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("\n" +
				"\n" +
				"select\n" +
				"          concat(ip.dx_country,'-',ip.dx_province ,'-',ip.dx_city ) ccName        ,\n" +
				"          rg.ip                                                     ip            ,\n" +
				"          ad.name                                                   adName        ,\n" +
				"          wpc.chnname                                               chlName       ,\n" +
				"          pc.chnname                                                parentChlName ,\n" +
				"          rg.d_ad_id                                                adId          ,\n" +
				"          d.gname                                                   parentGameName,\n" +
				"          c.gname                                                   gameName      ,\n" +
				"          r.role_name                                               roleName      ,\n" +
				"          rg.role_id                                                roleId        ,\n" +
				"          rg.orderno                                                orderNo       ,\n" +
				"          rg.tradeno                                                tradeNo       ,\n" +
				"          rg.d_reg_time                                             regTime       ,\n" +
				"          rg.pay_time                                               payTime       ,\n" +
				"          rg.area_id                                                areaId        ,\n" +
				"          rg.pid                                                    productId     ,\n" +
				"          rg.pname                                                  productName   ,\n" +
				"          rg.status                                                 status        ,\n" +
				"          rg.rtype                                                  rType         ,\n" +
				"          rg.fee_decimal + rg.givemoney        totalFee      ,\n" +
				"          rg.fee_decimal                                            fee           ,\n" +
				"          rg.givemoney                                              giveMoney     ,\n" +
				"          r.ucid                                                   ucId          ,\n"+
				"          rg.username                                               userName\n" +
				"from\n" +
				"          (\n" +
				"                 select\n" +
				"                        a.ip             ,\n" +
				"                        a.d_ad_id        ,\n" +
				"                        a.role_id        ,\n" +
				"                        a.orderno        ,\n" +
				"                        a.tradeno        ,\n" +
				"                        a.pid            ,\n" +
				"                        b.reg_time as   d_reg_time   ,\n" +
				"                        a.pay_time       ,\n" +
				"                        a.area_id        ,\n" +
				"                        a.pid            ,\n" +
				"                        a.pname          ,\n" +
				"                        a.status         ,\n" +
				"                        a.rtype          ,\n" +
				"                        a.fee_decimal    ,\n" +
				"                        a.givemoney      ,\n" +
				"                        a.currency_amount,\n" +
				"                        a.username       ,\n" +
				"                        a.d_chl_sub      ,\n" +
				"                        a.d_chl_main     ,\n" +
				"                        a.d_chl_base     ,\n" +
				"                        a.game_sub       ,\n" +
				"                        a.game_main      ,\n" +
				"                        a.d_ad_account   ,\n" +
				"                        a.d_ctype        ,\n" +
				//"                        ucid       ,\n" +
				"                        a.create_time\n" +
				"                 from\n" )
				.append(yunYingProperties.getDeviceregisterRechargetable())
				.append("  a \n")
				.append("left join \n")
				.append("( select  \n")
				.append("username,min(reg_time) as reg_time ,min(reg_day) as reg_day \n")
				.append("from  \n")
				.append(yunYingProperties.getAccountregistertable()).append("\n")
				.append("group by username  ").append(" )  b \n")
				.append("on a.username = b.username   \n")
				.append(" where 1=1  ")
				.append(selectCommonCondition(req,1)).append("\n")
				.append("  ) rg \n")
				.append(" left join ").append(yunYingProperties.getIpTable()).append(" ip \n")
				.append(" on ip.dx_short_ip =arrayStringConcat(arrayPopBack(splitByString('.',splitByString(',',rg.ip)[length(splitByString(',',rg.ip))])),'.')\n" )
				.append(" left join  ").append(yunYingProperties.getGameRoleTable()).append("  r \n") .append(" on r.role_id =rg.role_id and rg.game_main =r.pgame_id  and rg.area_id =toString(r.area_id )\n" )
				.append(" left join ")
				.append(yunYingProperties.getPanguadidtable()).append("  ad on ad.adid =rg.d_ad_id   AND ad.ctype = rg.d_ctype \n")
				.append(" left JOIN ")
				.append(yunYingProperties.getPromotionChannelV3Table()).append("  wpc   ON  wpc.parent_code = rg.d_chl_main AND wpc.chncode = rg.d_chl_sub  \n")
				.append(" left join  ").append(yunYingProperties.getPromotionChannelV3Table()).append("   pc     on pc.id =wpc.pid  \n")
				.append(" left JOIN  ").append(yunYingProperties.getAdAccountTable()) .append("  oaa on rg.d_ad_account = oaa.advertiser_id\n")
				.append(" left JOIN  ").append(yunYingProperties.getSysUserTable()).append("  osu ON oaa.throw_user = toString(osu.user_id)\n" )
				.append(" left join  ").append(yunYingProperties.getWanGameTable()).append("    c on c.id = rg.game_sub\n")
				.append(" left join  ").append(yunYingProperties.getParentGameTable()).append("   d on d.id = c.pgid  \n" )
				.append("where 1 =1 ")
				.append(selectCommonCondition(req,2)).append("\n");
		return sql;
	}








	//todo 筛选条件
	public String selectCommonCondition(ActivityRechargeDataDto req,Integer num) {
		String startTime = req.getStartTime();
		String endTime = req.getEndTime();
		String regStartTime = req.getRegStartTime();
		String regEndTime = req.getRegEndTime();
		 String pgidArr = req.getPgIdArr();
		 String gameidArr = req.getGameIdArr();
		String advertiserIdArr=req.getAdAccounts();
		String adIdArr=req.getAdidArr();
		String chlArr = req.getChlArr();
		 String parentchlArr = req.getParentChlArr();
		 String appchlArr = req.getAppChlArr();
		 String userIdArr=req.getUserIds();
		 String ucId = req.getUcId();
		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();
		if(1 == num){
			Date sDate=DateUtils.stringToDate(startTime, DateUtils.YYYY_MM_DD);
			startTime=DateUtils.dateToString(sDate,DateUtils.YYYYMMDD);

			Date eDate=DateUtils.stringToDate(endTime, DateUtils.YYYY_MM_DD);
			endTime=DateUtils.dateToString(eDate,DateUtils.YYYYMMDD);
			commCondSB.append(" and a.pay_day >= ").append(startTime)
					.append(StringUtils.SPACE)
					.append(" and a.pay_day <= ").append(endTime)
					.append(StringUtils.SPACE);

			if (StringUtils.isNotEmpty(regStartTime) && StringUtils.isNotEmpty(regEndTime)){
				commCondSB.append(" and b.reg_day >= ").append(regStartTime)
						.append(StringUtils.SPACE)
						.append(" and b.reg_day <= ").append(regEndTime)
						.append(StringUtils.SPACE);
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				commCondSB.append(" AND a.game_main IN (").append(pgidArr).append(")");

			}
			if (StringUtils.isNotBlank(gameidArr)) {

					commCondSB.append(" AND a.game_sub IN (").append(gameidArr).append(")");

			}
			if (StringUtils.isNotBlank(advertiserIdArr)) {
					// -- 广告权限
					commCondSB.append("  AND a.d_ad_account IN  (").append(advertiserIdArr).append(")");

			}
			if (StringUtils.isNotBlank(adIdArr)) {
				commCondSB.append(" AND a.d_ad_id IN ('").append(adIdArr.replaceAll(",", "', '")).append("')");
			}
			if (StringUtils.isNotBlank(chlArr)) {
				commCondSB.append(" AND a.d_chl_sub IN ('").append(chlArr.replaceAll(",", "', '")).append("')");
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				commCondSB.append(" AND a.d_chl_main IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				commCondSB.append(" AND a.d_chl_base IN ('").append(appchlArr.replaceAll(",", "', '")).append("')");
			}


		}else if(2 == num){
			if(StringUtils.isNotBlank(userIdArr)){
				if (req.getIsSys() != 1) {
					commCondSB.append(" AND  osu.user_id  IN (").append(req.getUserIds()).append(")");
				}
			}
			if (StringUtils.isNotBlank(req.getRoleName())){
				commCondSB.append(" AND  r.role_name  like '%").append(req.getRoleName()).append("%'");
			}
			//ucid匹配
			if (StringUtils.isNotEmpty(ucId)){
				commCondSB.append(" AND r.ucid   like '%").append(ucId).append("%'");
			}

		}





		return commCondSB.toString();
	}



}
